
create table #ZZMQ00(
	AddressNumber	INTEGER,
	LastInvoiceDate	DECIMAL(19, 0),
	DateOfFirstInvoice	DECIMAL(19, 0))

insert into #ZZMQ00
select	distinct a12.ShiptoNumber  AddressNumber,
	a12.LastInvoiceDate  LastInvoiceDate,
	a12.DateOfFirstInvoice  DateOfFirstInvoice
from	MicroStrategy_Views.dbo.Common_DeliveryDayCodeDetails	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.Frequency = a12.StopCode)
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a13
	  on 	(a12.LastInvoiceDate = a13.CompanyDateID)
where	(((a12.ShiptoNumber)
 in	(select	r11.ShiptoNumber
	from	MicroStrategy_Views.dbo.Sales_Star_Customer	r11
		join	MicroStrategy_Views.dbo.Common_DeliveryDayCodeDetails	r12
		  on 	(r11.StopCode = r12.Frequency)
		join	MicroStrategy_Views.dbo.Common_Star_Dates	r13
		  on 	(r11.LastInvoiceDate = r13.CompanyDateID)
	where	DATEDIFF(DD, r13.Dates, '2012-04-06')>r12.Days))
 and DATEDIFF(DD, a13.Dates, '2012-04-06')>a11.Days
 and a12.ShiptoSearchTypeCode in ('CFS', 'CCS', 'CFD'))OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD01(
	ShiptoSearchTypeCode	CHAR(3),
	AddressNumber	INTEGER,
	WJXBFS1	VARCHAR(255))

insert into #ZZMD01
select	a11.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	pa12.AddressNumber  AddressNumber,
	max(a11.HOLDORDERSCODEDescription)  WJXBFS1
from	MicroStrategy_Views.dbo.Sales_Star_Customer	a11
	join	#ZZMQ00	pa12
	  on 	(a11.DateOfFirstInvoice = pa12.DateOfFirstInvoice and
	a11.LastInvoiceDate = pa12.LastInvoiceDate and
	a11.ShiptoNumber = pa12.AddressNumber)
where	a11.Category05Code in ('O08')
group by	a11.ShiptoSearchTypeCode,
	pa12.AddressNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD02(
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT)

insert into #ZZMD02
select	a12.AddressNumberLessorRentorMort  AddressNumber,
	sum(a11.PriorYearCloseBalance)  WJXBFS1
from	MicroStrategy_Views.dbo.FA_Star_Facts	a11
	join	MicroStrategy_Views.dbo.FA_Star_AssetMaster	a12
	  on 	(a11.AssetItemNumberID = a12.AssetItemNumberID)
where	(((a12.AddressNumberLessorRentorMort)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
 and a11.AssetAccountType in ('1', '2')
 and ((a12.AddressNumberLessorRentorMort)
 in	(select	c21.ShiptoNumber
	from	MicroStrategy_Views.dbo.Sales_Star_Customer	c21)))
group by	a12.AddressNumberLessorRentorMort OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD03(
	AddressNumber	INTEGER,
	WJXBFS1	INTEGER)

insert into #ZZMD03
select	a11.AddressNumberLessorRentorMort  AddressNumber,
	count(a11.AssetItemNumberID)  WJXBFS1
from	MicroStrategy_Views.dbo.FA_Star_AssetMaster	a11
where	((a11.AddressNumberLessorRentorMort)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
group by	a11.AddressNumberLessorRentorMort OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD04(
	AddressNumber	INTEGER,
	WJXBFS1	DATETIME)

insert into #ZZMD04
select	a12.ShiptoNumber  AddressNumber,
	DATEDIFF(DD, Convert(Datetime, max(a11.Dates)) , '2012-04-06')  WJXBFS1
from	MicroStrategy_Views.dbo.Common_Star_Dates	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.CompanyDateID = a12.LastInvoiceDate)
where	(exists (select	*
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')
	 and	ps21.DateOfFirstInvoice = a12.DateOfFirstInvoice
	 and 	ps21.AddressNumber = a12.ShiptoNumber))
group by	a12.ShiptoNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD05(
	ShiptoSearchTypeCode	CHAR(3),
	AddressNumber	INTEGER,
	WJXBFS1	INTEGER)

insert into #ZZMD05
select	a12.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	pa13.AddressNumber  AddressNumber,
	max(a11.Days)  WJXBFS1
from	MicroStrategy_Views.dbo.Common_DeliveryDayCodeDetails	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.Frequency = a12.StopCode)
	join	#ZZMQ00	pa13
	  on 	(a12.DateOfFirstInvoice = pa13.DateOfFirstInvoice and
	a12.LastInvoiceDate = pa13.LastInvoiceDate and
	a12.ShiptoNumber = pa13.AddressNumber)
where	a12.Category05Code in ('O08')
group by	a12.ShiptoSearchTypeCode,
	pa13.AddressNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD06(
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT)

insert into #ZZMD06
select	a12.CustomerNumber  AddressNumber,
	sum(a11.TransactionOpenAmount)  WJXBFS1
from	MicroStrategy_Views.dbo.AR_STAR_Facts	a11
	join	MicroStrategy_Views.dbo.AR_Star_Customer	a12
	  on 	(a11.CustomerNumberID = a12.CustomerNumberID)
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a13
	  on 	(a11.DueDateID = a13.CompanyDateID)
where	(((a12.CustomerNumber)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
 and a13.Dates > CONVERT(datetime, '2012-03-03 00:00:00', 120)
 and ((a12.CustomerNumber)
 in	(select	c21.ShiptoNumber
	from	MicroStrategy_Views.dbo.Sales_Star_Customer	c21)))
group by	a12.CustomerNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD07(
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT)

insert into #ZZMD07
select	a12.CustomerNumber  AddressNumber,
	sum(a11.TransactionOpenAmount)  WJXBFS1
from	MicroStrategy_Views.dbo.AR_STAR_Facts	a11
	join	MicroStrategy_Views.dbo.AR_Star_Customer	a12
	  on 	(a11.CustomerNumberID = a12.CustomerNumberID)
where	(a12.CustomerNumber)
 in	(((select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08'))
 intersect (select	c21.ShiptoNumber
	from	MicroStrategy_Views.dbo.Sales_Star_Customer	c21)))
group by	a12.CustomerNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD08(
	ShiptoSearchTypeCode	CHAR(3),
	AddressNumber	INTEGER,
	WJXBFS1	DATETIME)

insert into #ZZMD08
select	a12.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	pa13.AddressNumber  AddressNumber,
	max(a11.Dates)  WJXBFS1
from	MicroStrategy_Views.dbo.Common_Star_Dates	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.CompanyDateID = a12.DateOfFirstInvoice)
	join	#ZZMQ00	pa13
	  on 	(a11.CompanyDateID = pa13.DateOfFirstInvoice and
	a12.LastInvoiceDate = pa13.LastInvoiceDate and
	a12.ShiptoNumber = pa13.AddressNumber)
where	a12.Category05Code in ('O08')
group by	a12.ShiptoSearchTypeCode,
	pa13.AddressNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD09(
	ShiptoSearchTypeCode	CHAR(3),
	AddressNumber	INTEGER,
	WJXBFS1	DATETIME)

insert into #ZZMD09
select	a12.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	pa13.AddressNumber  AddressNumber,
	max(a11.Dates)  WJXBFS1
from	MicroStrategy_Views.dbo.Common_Star_Dates	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.CompanyDateID = a12.LastInvoiceDate)
	join	#ZZMQ00	pa13
	  on 	(a11.CompanyDateID = pa13.LastInvoiceDate and
	a12.DateOfFirstInvoice = pa13.DateOfFirstInvoice and
	a12.ShiptoNumber = pa13.AddressNumber)
where	a12.Category05Code in ('O08')
group by	a12.ShiptoSearchTypeCode,
	pa13.AddressNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD0A(
	ShiptoSearchTypeCode	CHAR(3),
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT)

insert into #ZZMD0A
select	a13.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ShiptoNumber  AddressNumber,
	sum(a11.QuantityShipped)  WJXBFS1,
	sum(a11.ExtendedPrice)  WJXBFS2
from	MicroStrategy_Views.dbo.Sales_Star_Facts	a11
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a12
	  on 	(a11.GLDateID = a12.CompanyDateID)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
where	(((a11.ShiptoNumber)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
 and a11.TransactionType in (2)
 and a12.FiscalYear in (select distinct(FiscalYear) -1 from MicroStrategy_Views.dbo.Common_Star_Dates where Dates = '2012-04-06')
 and a12.FiscalMonth between '01' and (select distinct(FiscalMonth) from MicroStrategy_Views.dbo.Common_Star_Dates where Dates = '2012-04-06'))
group by	a13.ShiptoSearchTypeCode,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ShiptoNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD0B(
	ShiptoSearchTypeCode	CHAR(3),
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT)

insert into #ZZMD0B
select	a12.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a12.ShiptoNumber  AddressNumber,
	sum(a11.PL_UnitPrice)  WJXBFS1
from	MicroStrategy_Views.dbo.Sales_Star_Price_Facts	a11
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a12
	  on 	(a11.CustomerNumberID = a12.CustomerNumberID)
	join	MicroStrategy_Views.dbo.Sales_Star_PriceLedger	a13
	  on 	(a11.LineNumber = a13.LineNumber and
	a11.OrderNumber = a13.OrderNumber and
	a11.OrderNumberDocumentCompany = a13.OrderNumberDocumentCompany and
	a11.OrderTypeCode = a13.OrderTypeCode and
	a11.PriceAdjustmentKeyID = a13.PriceAdjustmentKeyID and
	a11.SourceID = a13.SourceID)
where	(((a12.ShiptoNumber)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
 and a13.SequenceNumber between 902 and 998)
group by	a12.ShiptoSearchTypeCode,
	a11.OrderNumber,
	a11.LineNumber,
	a12.ShiptoNumber OPTION (MERGE JOIN, LOOP JOIN)

create table #ZZMD0C(
	ShiptoSearchTypeCode	CHAR(3),
	OrderNumber	INTEGER,
	LineNumber	DECIMAL(20, 7),
	AddressNumber	INTEGER,
	WJXBFS1	FLOAT,
	WJXBFS2	FLOAT)

insert into #ZZMD0C
select	a13.ShiptoSearchTypeCode  ShiptoSearchTypeCode,
	a11.OrderNumber  OrderNumber,
	a11.LineNumber  LineNumber,
	a11.ShiptoNumber  AddressNumber,
	sum(a11.ExtendedPrice)  WJXBFS1,
	sum(a11.QuantityShipped)  WJXBFS2
from	MicroStrategy_Views.dbo.Sales_Star_Facts	a11
	join	MicroStrategy_Views.dbo.Common_Star_Dates	a12
	  on 	(a11.GLDateID = a12.CompanyDateID)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a13
	  on 	(a11.CustomerNumberID = a13.CustomerNumberID)
where	(((a11.ShiptoNumber)
 in	(select	ps21.AddressNumber
	from	#ZZMQ00	ps21
		join	MicroStrategy_Views.dbo.Sales_Star_Customer	s23
		  on 	(ps21.AddressNumber = s23.ShiptoNumber)
	where	s23.Category05Code in ('O08')))
 and a11.TransactionType in (2)
 and a12.FiscalYear in (select distinct(FiscalYear) from MicroStrategy_Views.dbo.Common_Star_Dates where Dates = '2012-04-06')
 and a12.FiscalMonth between '01' and (select distinct(FiscalMonth) from MicroStrategy_Views.dbo.Common_Star_Dates where Dates = '2012-04-06'))
group by	a13.ShiptoSearchTypeCode,
	a11.OrderNumber,
	a11.LineNumber,
	a11.ShiptoNumber OPTION (MERGE JOIN, LOOP JOIN)

select	distinct coalesce(pa11.LineNumber, pa12.LineNumber, pa13.LineNumber)  LineNumber,
	coalesce(pa11.OrderNumber, pa12.OrderNumber, pa13.OrderNumber)  OrderNumber,
	coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber)  AddressNumber,
	a113.AlphaName  AlphaName,
	a114.StopCode  StopCode,
	a113.Category03Code  Category03Code,
	a113.Category03  Category03,
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode, pa13.ShiptoSearchTypeCode)  ShiptoSearchTypeCode,
	a113.Category04Code  Category04Code,
	a113.Category04  Category04,
	a113.Category05Code  Category05Code,
	a113.Category05  Category05,
	a113.Category06Code  Category06Code,
	a113.Category06  Category06,
	pa14.WJXBFS1  WJXBFS1,
	pa15.WJXBFS1  WJXBFS2,
	pa16.WJXBFS1  WJXBFS3,
	pa17.WJXBFS1  WJXBFS4,
	pa18.WJXBFS1  WJXBFS5,
	pa19.WJXBFS1  WJXBFS6,
	pa110.WJXBFS1  WJXBFS7,
	pa111.WJXBFS1  WJXBFS8,
	pa112.WJXBFS1  WJXBFS9,
	ISNULL((pa11.WJXBFS1 * pa12.WJXBFS1), 0)  WJXBFSa,
	pa13.WJXBFS1  WJXBFSb,
	ISNULL((pa13.WJXBFS2 * pa12.WJXBFS1), 0)  WJXBFSc,
	pa11.WJXBFS2  WJXBFSd
from	#ZZMD0A	pa11
	full outer join	#ZZMD0B	pa12
	  on 	(pa11.AddressNumber = pa12.AddressNumber and
	pa11.LineNumber = pa12.LineNumber and
	pa11.OrderNumber = pa12.OrderNumber and
	pa11.ShiptoSearchTypeCode = pa12.ShiptoSearchTypeCode)
	full outer join	#ZZMD0C	pa13
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber) = pa13.AddressNumber and
	coalesce(pa11.LineNumber, pa12.LineNumber) = pa13.LineNumber and
	coalesce(pa11.OrderNumber, pa12.OrderNumber) = pa13.OrderNumber and
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode) = pa13.ShiptoSearchTypeCode)
	left outer join	#ZZMD01	pa14
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa14.AddressNumber and
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode, pa13.ShiptoSearchTypeCode) = pa14.ShiptoSearchTypeCode)
	left outer join	#ZZMD02	pa15
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa15.AddressNumber)
	left outer join	#ZZMD03	pa16
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa16.AddressNumber)
	left outer join	#ZZMD04	pa17
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa17.AddressNumber)
	left outer join	#ZZMD05	pa18
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa18.AddressNumber and
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode, pa13.ShiptoSearchTypeCode) = pa18.ShiptoSearchTypeCode)
	left outer join	#ZZMD06	pa19
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa19.AddressNumber)
	left outer join	#ZZMD07	pa110
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa110.AddressNumber)
	left outer join	#ZZMD08	pa111
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa111.AddressNumber and
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode, pa13.ShiptoSearchTypeCode) = pa111.ShiptoSearchTypeCode)
	left outer join	#ZZMD09	pa112
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = pa112.AddressNumber and
	coalesce(pa11.ShiptoSearchTypeCode, pa12.ShiptoSearchTypeCode, pa13.ShiptoSearchTypeCode) = pa112.ShiptoSearchTypeCode)
	join	MicroStrategy_Views.dbo.Common_Star_AddressBook	a113
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = a113.AddressNumber)
	join	MicroStrategy_Views.dbo.Sales_Star_Customer	a114
	  on 	(coalesce(pa11.AddressNumber, pa12.AddressNumber, pa13.AddressNumber) = a114.ShiptoNumber)


drop table #ZZMQ00

drop table #ZZMD01

drop table #ZZMD02

drop table #ZZMD03

drop table #ZZMD04

drop table #ZZMD05

drop table #ZZMD06

drop table #ZZMD07

drop table #ZZMD08

drop table #ZZMD09

drop table #ZZMD0A

drop table #ZZMD0B

drop table #ZZMD0C

